package com.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.domain.Account;
import com.domain.TabpleEmp;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Auther: ChenJinEn
 * @Date: 2020/7/27 20:42
 * @Description: //TODO
 */
public class JDBCUtil {
    public static void main(String[] args) throws Exception {
        System.out.println(JDBCUtil.getConnection());
        String[] arr = {"7","dd","313"};
        Connection con = JDBCUtil.getConnection();
        //con.setTransactionIsolation(2);//设置事务隔离级别为读已提交,避免并发时的脏读现象
                con.setAutoCommit(false);
        try {
            //通用增删改
            //changeTable1(con,"insert into account values(?,?,?)",arr);
            //changeTable("delete from account where id=?",7);

            //获取对象集合
            List<Account> list = find1(con,Account.class, "select * from account");
            List<TabpleEmp> list1 = find(TabpleEmp.class, "select * from tabple_emp");
            list.forEach(System.out::println);
            list1.forEach(System.out::println);

            //获取特殊单个值得演示
            Object value = getValue1(con,"select count(1) from account");
            System.out.println(value);
            Object value1 = getValue1(con,"select count(1) from tabple_emp");
            System.out.println(value1);
        } catch (Exception e) {
            System.out.println("程序出现异常,事务执行回滚操作");
            con.rollback();
        } finally {
            con.commit();
            con.setAutoCommit(true);
            con.close();
        }

    }

    //单表的通用增删改
    private static int changeTable(String sql, Object ...args) {
        Connection con = null;
        PreparedStatement pst = null;

        try {
            con = getConnection();
            pst = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1,args[i] );
            }
            return pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, pst, null);
        }
        return 0;
    }
    //单表的通用增删改(有事务版本)
    private static int changeTable1(Connection con,String sql, Object ...args) {
        PreparedStatement pst = null;

        try {
            pst = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1,args[i] );
            }
           return pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(null, pst, null);
        }
        return 0;
    }

    //通用查询
    public static <T>List<T> find(Class<T> cls,String sql, Object ...args){
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<T> list = new ArrayList<>();
        try {
            con = getConnection();
            pst = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1,args[i] );
            }
            rs = pst.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//获取结果集中查出的对象列数
            while (rs.next()) {
                T t = cls.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //String columnName = metaData.getColumnName(i + 1);//获取表的列的列名
                    String columnName = metaData.getColumnLabel(i + 1);//获取表的列的别名,针对于表名与实体类属性名不一致,在sql中取别名
                    Field field = cls.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return list;
    }

    //通用查询(有事务版)
    public static <T>List<T> find1(Connection con,Class<T> cls,String sql, Object ...args){
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<T> list = new ArrayList<>();
        try {
            pst = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1,args[i] );
            }
            rs = pst.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//获取结果集中查出的对象列数
            while (rs.next()) {
                T t = cls.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnName1 = metaData.getColumnName(i + 1);//获取表的列的列名
                    String columnName = metaData.getColumnLabel(i + 1);//获取表的列的别名,针对于表名与实体类属性名不一致,在sql中取别名
                    Object columnValue = rs.getObject(columnName1);//获取列属性对于的数据库表中的值
                    Field field = cls.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(null, pst, rs);
        }
        return list;
    }

    //通用查询特殊值得方法(比如统计,求和,求平均值等操作sql)
    public static <E> E getValue(String sql, Object ...args){
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = JDBCUtil.getConnection();
            pst = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1, args[i]);
            }
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con, pst, null);
        }
        return null;
    }

    //通用查询特殊值得方法(比如统计,求和,求平均值等操作sql)考虑事务版本
    public static <E> E getValue1(Connection con,String sql, Object ...args){
        PreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1, args[i]);
            }
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(null, pst, null);
        }
        return null;
    }

    //获取连接
     private static ComboPooledDataSource c3p0 = new ComboPooledDataSource();//池子只需要一个
     private  static BasicDataSource dbcp = new BasicDataSource();
     private static  DruidDataSource druid = new DruidDataSource();


/*

        c3p0.setDriverClass("com.mysql.jdbc.Driver");
        c3p0.setJdbcUrl("jdbc:mysql://localhost:3306/db_cdm0menjin");
        c3p0.setUser("root");
        c3p0.setPassword("root");
        c3p0.setInitialPoolSize(10);
    Connection cpsConnection = c3p0.getConnection();
        DataSources.destroy(c3p0);//销毁连接池


        dbcp.setDriverClassName("com.mysql.jdbc.Driver");
        dbcp.setUrl("jdbc:mysql://localhost:3306/db_cdm0menjin");
        dbcp.setUsername("root");
        dbcp.setPassword("root");
        dbcp.setInitialSize(10);
    Connection dbcpConnection = dbcp.getConnection();

    Properties ps = new Properties();
        ps.load(JDBCUtil.class.getClassLoader().getResourceAsStream("dbcp.properties"));
    DataSource dataSource = BasicDataSourceFactory.createDataSource(ps);
    Connection dataSourceConnection = dataSource.getConnection();


        druid.setDriverClassName("com.mysql.jdbc.Driver");
        druid.setUrl("jdbc:mysql://localhost:3306/db_cdm0menjin");
        druid.setUsername("root");
        druid.setPassword("root");
        druid.setInitialSize(10);

        Class.forName("oracle.jdbc.driver.OracleDriver");
        return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "123");
*/

    @Test
    public static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/db_cdm0menjin", "root", "root");
    }

    //释放资源
    public static void close(Connection con, PreparedStatement pst, ResultSet rs){
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }




}
